DECLARE @Find AS VARCHAR (200)
SET @Find ='test'

SET @Find ='%'+@Find+'%'
SELECT DISTINCT OBJECT_NAME (asm.OBJECT_ID) ,o.type_desc
FROM   sys.all_sql_modules asm INNER JOIN sys.objects o ON o.[object_id]=asm.[object_id]
WHERE  definition LIKE @Find AND o.is_ms_shipped =0 
--       FOR XML PATH ('')
UNION
SELECT DISTINCT OBJECT_NAME (ID),o.type_desc
FROM   syscomments asm INNER JOIN sys.objects o ON o.[object_id]=asm.ID
WHERE  TEXT LIKE @find AND o.is_ms_shipped =0 AND o.name NOT LIKE 'SP_%'
ORDER BY 1



